﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using ERP_RORZE_SYSTEM;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class panel_ExportPlan : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;
        
        public panel_ExportPlan()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);
            LoadData();
            ExportPlan.PopupMenuShowing += ExportPlan_PopupMenuShowing;
            exportPlanContext.ItemClicked += exportPlanContext_ItemClicked;
        }

        void exportPlanContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.exportPlanContext.Hide();
            switch (e.ClickedItem.Name)
            {
                case "importSelection":
                    importDatabase();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void ExportPlan_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            exportPlanContext.Show(MousePosition);            
        }

        //////////////////////////////////////////////
        /// Load Data to Grid View
        //////////////////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT t1.[ID], t1.[PlanNo], t2.[FullName], t2.[PIC], t1.[RobotNo], t1.[Quantity], t1.[DocFinished],"
                + " t1.[Exported], t1.[ExportedDate]"
                + " FROM [241_ExportPlan] AS t1 LEFT JOIN"
                + " [222_Customer] AS t2 ON t1.[CustomerID]=t2.[CustomerID]";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridExportPlan.DataSource = sourceDataSet.Tables[0];
                getUpdateInfo();
            } 
            else
            {
                MessageBox.Show("There is no data for Export Plan");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(string));
                newTable.Columns.Add("PlanNo", typeof(string));
                newTable.Columns.Add("FullName", typeof(string));
                newTable.Columns.Add("PIC", typeof(string));
                newTable.Columns.Add("RobotNo", typeof(string));
                newTable.Columns.Add("Quantity", typeof(int));
                newTable.Columns.Add("DocFinished", typeof(int));
                newTable.Columns.Add("Exported", typeof(int));
                newTable.Columns.Add("ExportedDate", typeof(int));
                gridExportPlan.DataSource = newTable;
            }
            ExportPlan.Columns[0].Caption = "ID";
            ExportPlan.Columns[1].Caption = "Plan No";
            ExportPlan.Columns[2].Caption = "Customer Name";
            ExportPlan.Columns[3].Caption = "Customer PIC";
            ExportPlan.Columns[4].Caption = "RobotNo";
            ExportPlan.Columns[5].Caption = "Quantity";
            ExportPlan.Columns[6].Caption = "Doc Finished";
            ExportPlan.Columns[7].Caption = "Exported";
            ExportPlan.Columns[8].Caption = "ExportedDate";
            ExportPlan.Columns[0].Visible = false;
            ExportPlan.BestFitColumns();
        }       

        /////////////////////////////////////
        /// Get Update Information
        /////////////////////////////////////
        private void getUpdateInfo()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT LastUpdate FROM [222_UpdateHistory] WHERE ID=18";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    this.updateDate.Text = dataReader["LastUpdate"].ToString();
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get update information");
            }
        }

        ////////////////////////////////////
        /// Update database From AS400
        ////////////////////////////////////

        private void importDatabase()
        {
            DataSet as400 = new DataSet();
            DataSet exportplan = new DataSet();
            string sql = null;
            SqlCommand cmd;
            SqlDataAdapter adapter;
            DataTable exportplan_db2 = new DataTable();
            exportplan_db2.Columns.Add("PlanNo", typeof(string));
            exportplan_db2.Columns.Add("RobotNo", typeof(string));
            exportplan_db2.Columns.Add("Quantity", typeof(int));
            exportplan_db2.Columns.Add("CustomerID", typeof(string));
            DataTable exportplan_local;
            
            try
            {
                myConnection.Open();

                //Get Data From AS400 PLAN NOT SALE
                cmd = myConnection.CreateCommand();
                sql = "SELECT * FROM [VExportPlan1]";
                
                cmd.CommandText = sql;
                cmd.CommandTimeout = 10000;
                SqlDataReader datareader = cmd.ExecuteReader();                
                while (datareader.Read())
                {
                    DataRow new_row = exportplan_db2.NewRow();
                    new_row["PlanNo"] = datareader["PlanNo"].ToString().Trim();
                    new_row["RobotNo"] = datareader["RobotNo"].ToString().Trim();
                    float su = float.Parse(datareader["Quantity"].ToString().Trim());
                    new_row["Quantity"] = su;
                    new_row["CustomerID"] = datareader["CustomerID"].ToString().Trim();
                    exportplan_db2.Rows.Add(new_row);
                }
                datareader.Close();
                cmd.Dispose();
                                
                //Get Data From AS400 PLAN SALED
                cmd = myConnection.CreateCommand();
                sql = "SELECT * FROM [VExportPlan2]";
                cmd.CommandText = sql;
                cmd.CommandTimeout = 10000;
                datareader = cmd.ExecuteReader();
                while (datareader.Read())
                {
                    DataRow new_row = exportplan_db2.NewRow();
                    new_row["PlanNo"] = datareader["PLANNO"].ToString().Trim();
                    new_row["RobotNo"] = datareader["ROBOTNO"].ToString().Trim();
                    float su = float.Parse(datareader["QUANTITY"].ToString().Trim());
                    new_row["Quantity"] = su;
                    new_row["CustomerID"] = datareader["CUSTOMERID"].ToString();
                    exportplan_db2.Rows.Add(new_row);
                }
                datareader.Close();
                cmd.Dispose();
                
                //Get Data From Local Database
                
                sql = "SELECT * FROM [241_ExportPlan]";
                cmd = new SqlCommand(sql, myConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(exportplan, "241_ExportPlan");
                exportplan_local = exportplan.Tables["241_ExportPlan"];
                                
                int i = 0;
                
                foreach (DataRow as400_row in exportplan_db2.Rows)
                {                    
                    if (!string.IsNullOrEmpty(as400_row["PLANNO"].ToString().Trim()))
                    {
                        DataRow[] existed_rows = exportplan_local.Select("PlanNo='" + as400_row["PlanNo"].ToString().Trim() + "'"
                            + " AND RobotNo='" + as400_row["RobotNo"].ToString().Trim() + "'");
                        if (existed_rows.Length <= 0)
                        {                            
                            DataRow new_row = exportplan_local.NewRow();
                            new_row["PlanNo"] = as400_row["PlanNo"].ToString().Trim();
                            new_row["CustomerID"] = as400_row["CustomerID"].ToString().Trim();
                            new_row["RobotNo"] = as400_row["RobotNo"].ToString().Trim();
                            int su;
                            if (Int32.TryParse(as400_row["Quantity"].ToString().Trim(), out su))
                            {
                                new_row["Quantity"] = su;
                                exportplan_local.Rows.Add(new_row);
                            }

                            i++;
                        }
                    }                    
                }
                               

                SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                //Update to Local Database
                adapter.Update(exportplan, "241_ExportPlan");

                adapter.Dispose();
                cmd.Dispose();
                myConnection.Close();

                //Update History
                myConnection.Open();
                sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=18";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();
                MessageBox.Show("Database has been updated successfull. \r\nThere are " + i.ToString() + " plans imported");
                LoadData();              

            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot connect to AS400");
            }
        }

        private void buttonImport_Click(object sender, EventArgs e)
        {
            importDatabase();
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }
    }
}
